/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cbsrs.controller;

import com.cbsrs.model.Blocks;
import com.cbsrs.model.Colleges;
import com.cbsrs.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class BlockDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(CollegeDAO.class);

    public static List<Blocks> find(int subjectOfferingID) throws Exception {
        List<Blocks> blockList = new ArrayList<Blocks>();
        String sql = "SELECT\n"
                + "b.block_id,\n"
                + "b.block_name,\n"
                + "b.`year`,\n"
                + "IF(b.semester = 1, '1st Semester',IF(b.semester = 2, '2nd Semester','Summer')) as semester,\n"
                + "b.user_id,\n"
                + "b.create_ts,\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "pr.program_revision_id,\n"
                + "pr.program_revision_name,\n"
                + "p.program_id,\n"
                + "p.program_name\n"
                + "FROM\n"
                + "subject_offerings AS so\n"
                + "INNER JOIN blocks AS b ON b.subject_offering_id = so.subject_offering_id\n"
                + "INNER JOIN program_revisions AS pr ON b.program_revision_id = pr.program_revision_id\n"
                + "INNER JOIN programs AS p ON pr.program_id = p.program_id\n"
                + "WHERE so.subject_offering_id = ?";
        SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, subjectOfferingID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Blocks block;
        while (rs.next()) {
            block = new Blocks();
            block.setBlockID(rs.getInt("block_id"));
            block.setProgramID(rs.getInt("program_id"));
            block.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            block.setProgramRevisionID(rs.getInt("program_revision_id"));
            block.setSemester(rs.getString("semester"));
            block.setYear(rs.getInt("year"));
            block.setUserID(rs.getInt("user_id"));
            block.setSubjectOfferingName(rs.getString("subject_offering_name"));
            block.setProgramRevisionName(rs.getString("program_revision_name"));
            block.setBlockName(rs.getString("block_name"));
            block.setProgramName(rs.getString("program_name"));
            if (rs.getString("create_ts").isEmpty()) {
                block.setCreatTs("");
            } else {
                block.setCreatTs(dateFormat.format(rs.getDate("create_ts")));
            }
            blockList.add(block);
        }
        closeConnection(getConnectionObject());
        return blockList;
    }

    public static List<Blocks> findAll() throws Exception {
        List<Blocks> blockList = new ArrayList<Blocks>();
        String sql = "SELECT\n"
                + "b.block_id,\n"
                + "b.block_name,\n"
                + "b.`year`,\n"
                + "b.semester,\n"
                + "b.user_id,\n"
                + "b.create_ts,\n"
                + "so.subject_offering_id,\n"
                + "so.subject_offering_name,\n"
                + "pr.program_revision_id,\n"
                + "pr.program_revision_name,\n"
                + "p.program_id,\n"
                + "p.program_name\n"
                + "FROM\n"
                + "subject_offerings AS so\n"
                + "INNER JOIN blocks AS b ON b.subject_offering_id = so.subject_offering_id\n"
                + "INNER JOIN program_revisions AS pr ON b.program_revision_id = pr.program_revision_id\n"
                + "INNER JOIN programs AS p ON pr.program_id = p.program_id";
        SimpleDateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Blocks block;
        while (rs.next()) {
            block = new Blocks();
            block.setBlockID(rs.getInt("block_id"));
            block.setProgramID(rs.getInt("program_id"));
            block.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            block.setProgramRevisionID(rs.getInt("program_revision_id"));
            block.setSemester(rs.getString("semester"));
            block.setYear(rs.getInt("year"));
            block.setUserID(rs.getInt("user_id"));
            block.setSubjectOfferingName(rs.getString("subject_offering_name"));
            block.setProgramRevisionName(rs.getString("program_revision_name"));
            block.setBlockName(rs.getString("block_name"));
            block.setProgramName(rs.getString("program_name"));
            if (rs.getString("create_ts").isEmpty()) {
                block.setBlockName("");
            } else {
                block.setBlockName(dateFormat.format(rs.getDate("create_ts")));
            }
            blockList.add(block);
        }
        closeConnection(getConnectionObject());
        return blockList;
    }
}
